Create an Excel mapping template
To create a new template, select the New Template option from the Select a template list. In the New Template dialog, enter the name in the New Name field. To copy mappings from an existing template, check the Copy from box and select the source template from the list.
To open the new template for editing, select the Edit Template option from the menu. The Template Mapping window will be opened and it will contain a table with one empty row as shown below.
- First, select a Regime which will be used to create projects. Then, select a workbook to be used to map Excel cells to
- To create a mapping, click on
and the Select Mapping window will be opened as shown below. In the top left corner, choose which type of item you want to map: a user-entered variable from the Regime selected in the Template Mapping window, a project property or a project setting.
Note: Advanced users can manually edit the template by checking the Allow manual edit box. This option allows you to edit variable names and Excel ranges directly in the grid, and to copy cells into Excel for further editing. This option is used primarily to manage Excel references.
- Depending on the selected item, mapping settings will be displayed in the right-hand pane. For most items, there are several ways to specify those settings, in which case the Value Type field will be displayed (all options are described in the table below).
| Option | Description |
|---|---|
| Import Date | Sets the date on which the import was performed as the item's value. |
| Import User | Sets the name of the user who performed the import as the item's value. |
| User Entered | Opens an appropriate field (text, numeric, etc.) where you can specify the value. |
| Workbook Range | Opens the list of named ranges existing in the reference workbook. |
| Worksheet Range | Opens fields where you can select a worksheet and specify a particular cell range. |
- If you are mapping settings, the full list is given in the table below.
| Setting | Description |
|---|---|
| Project Name | Name of the imported project. Use the name of the imported file, select a cell range, enter a value manually, use the date of the import operation or the name of the user who performed it. |
| Start Year | Start year of imported projects. Enter a value manually, select a cell range or use the date of the import operation. |
| Periodicity | Periodicity of imported projects. Select a value from the list. |
| Escalation/Inflation Date | Escalation and inflation date of imported projects. Enter a value manually, select a cell range or use the date of the import operation. |
| Economic Limit Calculation Type | Indicates how a project's economic limit is determined. Select a cell range or manually select an option: Calculated or Specified Date. |
| Economic Limit Min Months to Evaluate | If you have selected the Calculated option for the Econ Limit Calculation Type setting, specify the minimum number of months |
| Economic Limit Specified Date | If you have selected the Specified Date option for the Econ Limit Calculation Type setting, set the date. Enter a value manually, select a cell range or use the date of the import operation. |
| Scenario Comment 1-5 | Comment in the project. Select a cell range, enter a value manually, or use the date of the import operation or the name of the user who performed it. |
| Scenario Weighting | Weighting of the imported scenario if you are planning to use weightings. Select a cell range or enter a value manually. |
| Turn on Sensitivities & Weightings | Indicates whether sensitivities and weighting will be used in the imported projects |
| Import Duration (Years) | Period for which data will be imported. Select a cell range or enter a value manually. |
| Import End Date | Enter a value manually, select a cell range or use the date of the import operation. |
| Import Start Date | Enter a value manually, select a cell range or use the date of the import operation. |
- If you are mapping variables, additional fields may become available depending on the variable type (see the table below).
| Field | Description |
|---|---|
| Load as scalar | Check this box to apply a single value to all periods. |
| Total in last cell | Check this box if the last row in the Excel file contains total values; they will be ignored during import. |
| Unit system | Unit system of imported data. |
| Scale size | Unit scale of imported data. |
| Currency | Currency of imported data. |
| Type | Type of imported data (real or nominal). |
| Conversion settings | Values can be converted from volume to rate or from rate to volume as required. |
- After you have created the mappings, click on OK to save the mapping and close the Select Mapping window. Now you need to select operations to be performed on values during import. Choose one of the options in the Operation column (the descriptions are given in the table below).
| Operation | Description |
|---|---|
| Add | Adds the imported value to the current value stored in |
| Merge | Merges imported values with existing values. If the imported range contains blanks as well as values, those blanks will be filled with values already contained in |
| Overwrite | Overwrites the current value stored in |
| Subtract | Subtracts the imported value from the value stored in |
| Multiply | Multiplies the imported value by the value stored in |
| Divide | Divides the imported value by the value stored in |
- The Factor column contains factors that will be applied to imported values. Factors are applied regardless of the operation selected in the Operation column. By default, the factor is 1. Factors can be used, for example, if you need to convert values from a custom unit system to a
- If you need to change mapping settings, check the Show Link Settings box and the mappings table will show the current settings. Settings are enabled and disabled depending on the variable's type. You can select a different value from the lists.
On the Import Working Interest tab you can specify cell ranges in Excel where information about partners' shares is stored. To map working interest:
- Select the category from the list at the top.
- Click on next to the partner who has a share in this category.
- In the pop-up window, select the worksheet where the WI information is stored.
- Click on
. The pop-up window will be collapsed and you will be taken to the reference Excel file. - Select the cell with the working interest and click on in the collapsed window.
- The window will be expanded and the selected cell's name will appear in the text field. If the Excel file contains a sequence of working interest values, they will be loaded as reversions; to prevent this, check the Use first value only box.
- Click OK. The pop-up window will be closed and you will return to the Template Mappings window.
-
If you do not have WI shares for all partners (that is, the total share is less than 100%), you need to select a partner, to whom the remainder will be assigned, by checking the box next to its name in the Remainder of Share column. Note that the remainder has to be allocated even if the variable category is marked as optional in the Regime (see The General tab).
If the sum of all partners' shares exceeds 100%, only 100% will be loaded: partners whose shares add to less than 100% with the remainder allocated to the partner which has the Remainder of Share box checked.
Example: In the Excel file, Company A has 45% and Company B has 65%. In the mapping template, both partners are mapped to the corresponding cells in the workbook with the remainder allocated to Company C. As the total share is 115%, in the loaded project Company A will have 45% (it is less that 100%) and Company C will have 55%. Company B will be omitted as its share will bring the total to over 100% but, since the total cannot be less than 100%, Company C will be included with the remaining 55% allocated to it.